﻿using Apps.Common;
using Apps.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Apps.DAL.HC
{
    public partial class HC_ITEMS_Repository : DbContext<HC_ITEMS>
    {
        HC_EXTEND_FIELD_Repository fieldDal = new HC_EXTEND_FIELD_Repository();
        public bool DoAdd(HC_ITEMS_EXTEND itemsExtendModel)
        {
            try
            {
                List<HC_ITEM_EXTEND_REL> relList = new List<HC_ITEM_EXTEND_REL>();
                Db.Ado.BeginTran();
                HC_ITEMS hC_ITEMS = new HC_ITEMS()
                {
                    ID = ResultHelper.NewId,
                    ITEM_NAME = itemsExtendModel.ITEM_NAME.ToUpper(),
                    ITEM_TITLE = itemsExtendModel.ITEM_TITLE,
                    ENABLE = itemsExtendModel.ENABLE,
                    SORT = itemsExtendModel.SORT,
                    ADDTIME = DateTime.Now
                };
                string fieldIds = itemsExtendModel.FIELDS;//存储已加截的扩展字段ID集合
                Db.Insertable(hC_ITEMS).ExecuteCommand();
                if (fieldIds.Length > 0)
                {
                    foreach (var item in fieldIds.Split(','))
                    {
                        relList.Add(new HC_ITEM_EXTEND_REL()
                        {
                            ID = ResultHelper.NewId,
                            EXTEND_FIELD_ID = item,
                            ITEM_ID = hC_ITEMS.ID
                        });
                    }
                }
                Db.Insertable(relList).ExecuteCommand();

                //创建化产表单表
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("CREATE TABLE \"C##FIS\".\"HC_{0}\" (", hC_ITEMS.ITEM_NAME.ToUpper());
                strSql.Append("\"ID\" NVARCHAR2(50) DEFAULT SYS_GUID() NOT NULL ENABLE,\r\n");
                strSql.Append("\"ADDTIME\" DATE DEFAULT SYSDATE,\r\n");
                strSql.Append("\"ADDUSER\" NVARCHAR2(150) DEFAULT NULL,\r\n");
                strSql.Append("\"UPDATETIME\" DATE DEFAULT SYSDATE,\r\n");
                strSql.Append("\"UPDATEUSER\" NVARCHAR2(150) DEFAULT NULL,\r\n");
                strSql.Append("\"AUDITTIME\" DATE DEFAULT SYSDATE,\r\n");
                strSql.Append("\"AUDITSER\" NVARCHAR2(150) DEFAULT NULL,\r\n");
                strSql.Append("\"STATE\" NUMBER(1) DEFAULT 0,\r\n");
                strSql.Append("\"PLATENUMBER\" NVARCHAR2(50) DEFAULT NULL,\r\n");
                if (relList != null && relList.Count > 0)
                {
                    //查询扩展字段表
                    List<HC_EXTEND_FIELD> fieldList = new HC_EXTEND_FIELD_Repository().GetList(fieldIds);
                    //判断及组合创表SQL语句
                    foreach (var item in fieldList)
                    {
                        if (item.CONTROL_TYPE == 1)
                        {
                            strSql.AppendFormat("\"{0}\" NVARCHAR2(200) DEFAULT NULL,\r\n", item.NAME.ToUpper());
                        }
                        else
                        {
                            strSql.AppendFormat("\"{0}\" NUMBER(8,3) DEFAULT 0,\r\n", item.NAME.ToUpper());
                        }
                    }
                }
                strSql.Append("PRIMARY KEY (\"ID\"))");
                List<SugarParameter> para = new List<SugarParameter>() { };
                Db.Ado.GetInt(strSql.ToString(), para);
                Db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                Db.Ado.RollbackTran();
                return false;
            }
        }
        public List<HC_ITEMS> GetList(string queryStr, int pageIndex, int pageSize, string sortFiled, string orderBy, ref int totalCount)
        {
            var exp = Expressionable.Create<HC_ITEMS>();
            exp.Or(it => it.ITEM_NAME.Contains(queryStr));//拼接OR
            //exp.Or(it => it.TITLE.Contains(queryStr));//拼接OR
            return Db.Queryable<HC_ITEMS>().OrderBy(sortFiled + " " + orderBy).Where(exp.ToExpression()).ToPageList(pageIndex, pageSize, ref totalCount);
        }

        public bool DoEdit(HC_ITEMS_EXTEND itemsExtendModel)
        {
            string fieldIds = string.Empty;//存储已加截的扩展字段ID集合
            string newFieldIds = string.Empty; //用来存储新增的字段ID
            List<SugarParameter> alterPara = new List<SugarParameter>() { };
            List<HC_ITEM_EXTEND_REL> relList = new List<HC_ITEM_EXTEND_REL>();
            try
            {
                var oldModel = GetById(itemsExtendModel.ID);
                if (oldModel.fieldList == null) return false;
                Db.Ado.BeginTran();
                //string[] columns = { "ADDTIME" };
                //Db.Updateable(itemsModel).IgnoreColumns(columns).ExecuteCommand();
                //Db.Deleteable<HC_ITEM_EXTEND_REL>(x => x.ITEM_ID == itemsModel.ID).ExecuteCommand();
                //Db.Insertable(itemsModel.fieldList).ExecuteCommand();
                string[] columns = { "ADDTIME" };
                HC_ITEMS hC_ITEMS = new HC_ITEMS()
                {
                    ID = itemsExtendModel.ID,
                    ITEM_NAME = itemsExtendModel.ITEM_NAME,
                    ITEM_TITLE = itemsExtendModel.ITEM_TITLE,
                    ENABLE = itemsExtendModel.ENABLE,
                    SORT = itemsExtendModel.SORT,
                    UPDATETIME=DateTime.Now
                };
                if (itemsExtendModel.FIELDS.Length > 0)
                {
                    foreach (var item in itemsExtendModel.FIELDS.Split(','))
                    {
                        relList.Add(new HC_ITEM_EXTEND_REL()
                        {
                            ID = ResultHelper.NewId,
                            EXTEND_FIELD_ID = item,
                            ITEM_ID = hC_ITEMS.ID
                        });
                    }
                }
                hC_ITEMS.fieldList = relList;
                Db.Updateable(hC_ITEMS).IgnoreColumns(columns).ExecuteCommand();
                foreach (var modelt in oldModel.fieldList)
                {
                    //查找对应的字段ID，不在旧实体则删除
                    if (hC_ITEMS.fieldList.Find(p => p.EXTEND_FIELD_ID == modelt.EXTEND_FIELD_ID) == null)
                    {
                        //记住要删除的字段ID
                        fieldIds += modelt.EXTEND_FIELD_ID + ",";
                        //删除该旧字段
                        Db.Deleteable<HC_ITEM_EXTEND_REL>().Where(it => it.ITEM_ID == itemsExtendModel.ID && it.EXTEND_FIELD_ID == modelt.EXTEND_FIELD_ID).ExecuteCommand();
                    }
                }
                //删除频道数据表列
                if (fieldIds.Length > 0)
                {
                    //查询扩展字段表
                    List<HC_EXTEND_FIELD> fieldList = new HC_EXTEND_FIELD_Repository().GetList(fieldIds);
                    foreach (var item in fieldList)
                    {
                        string sql = string.Format("ALTER TABLE HC_{0} DROP COLUMN \"{1}\"", itemsExtendModel.ITEM_NAME, item.NAME.ToUpper());
                        Db.Ado.GetInt(sql.ToString(), alterPara);
                    }
                }

                if (hC_ITEMS.fieldList != null)
                {
                    //添加扩展字段
                    StringBuilder strSql1;
                    foreach (var modelt in hC_ITEMS.fieldList)
                    {
                        strSql1 = new StringBuilder();
                        HC_ITEM_EXTEND_REL fieldModel = null;
                        if (oldModel.fieldList != null)
                        {
                            fieldModel = oldModel.fieldList.Find(p => p.EXTEND_FIELD_ID == modelt.EXTEND_FIELD_ID); //查找是否已经存在
                        }
                        if (fieldModel == null) //如果不存在则添加
                        {
                            newFieldIds += modelt.EXTEND_FIELD_ID + ","; //以逗号分隔开存储
                            Db.Insertable(modelt).ExecuteCommand();
                        }
                    }
                    //添加频道数据表列
                    if (newFieldIds.Length > 0)
                    {
                        //查询扩展字段表
                        List<HC_EXTEND_FIELD> newfieldList = new HC_EXTEND_FIELD_Repository().GetList(newFieldIds);
                        foreach (var item in newfieldList)
                        {
                            string sql = string.Empty;
                            if (item.CONTROL_TYPE == 1)
                            {
                                sql = string.Format("ALTER TABLE HC_{0} ADD {1} NVARCHAR2(150) DEFAULT NULL", itemsExtendModel.ITEM_NAME, item.NAME.ToUpper());
                            }
                            else if (item.CONTROL_TYPE == 3)
                            {
                                sql = string.Format("ALTER TABLE HC_{0} ADD {1} NVARCHAR2(150) DEFAULT NULL", itemsExtendModel.ITEM_NAME, item.NAME.ToUpper());
                            }
                            else
                            {
                                sql = string.Format("ALTER TABLE HC_{0} ADD {1} NUMBER(8,3) DEFAULT 0", itemsExtendModel.ITEM_NAME, item.NAME.ToUpper());
                            }
                            Db.Ado.GetInt(sql.ToString(), alterPara);
                        }
                    }

                }
                Db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                Db.Ado.RollbackTran();
                return false;
            }
            //return Db.Updateable<HC_ITEMS>(itemsModel).ExecuteCommand() > 0;
        }

        public bool DoDelete(string id)
        {
            List<SugarParameter> alterPara = new List<SugarParameter>() { };
            try
            {
                var items = GetById(id);

                //删除扩展字段表
                Db.Ado.BeginTran();
                Db.Deleteable<HC_ITEM_EXTEND_REL>().Where(x => x.ITEM_ID == id).ExecuteCommand();

                //删除化产数据表
                string dropSql = string.Format("DROP TABLE HC_{0}", items.ITEM_NAME);
                Db.Ado.GetInt(dropSql.ToString(), alterPara);

                //删除化产项表
                Db.Deleteable<HC_ITEMS>().Where(it => it.ID == id).ExecuteCommand();

                Db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                Db.Ado.RollbackTran();
                return false;
            }
            //return Db.Deleteable<HC_ITEMS>().Where(it => it.ID == id).ExecuteCommand() > 0;
        }

        public bool AddIsRepeated(string filedStr)
        {
            var exp = Expressionable.Create<HC_ITEMS>();
            exp.Or(it => it.ITEM_NAME.Contains(filedStr));//拼接OR
            //exp.Or(it => it.TITLE.Contains(filedStr));//拼接OR
            return Db.Queryable<HC_ITEMS>().Any(exp.ToExpression());
        }

        public bool EditIsRepeated(string id, string filedStr)
        {
            var exp = Expressionable.Create<HC_ITEMS>();
            exp.Or(it => it.ITEM_NAME.Contains(filedStr));//拼接OR
            //exp.Or(it => it.TITLE.Contains(filedStr));//拼接OR
            return Db.Queryable<HC_ITEMS>().Where(it => it.ID != id).Any(exp.ToExpression());
        }

        public HC_ITEMS GetById(string id)
        {
            var items = Db.Queryable<HC_ITEMS>().Single(it => it.ID == id);
            if (items != null)
            {
                if (items.fieldList == null)
                {
                    items.fieldList = Db.Queryable<HC_ITEM_EXTEND_REL>().Where(it => it.ITEM_ID == id).ToList();
                }
            }
            return items;
        }

        public List<HC_ITEMS> GetList(string queryStr)
        {
            return Db.Queryable<HC_ITEMS>()
                .OrderBy(x => x.SORT)
                .Where(x => x.ENABLE == 1)
                .WhereIF(!string.IsNullOrEmpty(queryStr), it => it.ITEM_TITLE.Contains(queryStr)).ToList();
        }

        public HC_ITEM_FIELD_VO GetFieldInfoByItemId(string itemId)
        {
            HC_ITEM_FIELD_VO vo = new HC_ITEM_FIELD_VO();
            vo.itemId = itemId;
            vo.itemVo = Db.Queryable<HC_ITEMS>().Where(o => o.ID == itemId).Select(x => new HC_ITEMS_VO
            {
                ITEM_ID = x.ID,
                ITEM_NAME = x.ITEM_NAME,
                ITEM_TITLE = x.ITEM_TITLE
            }).Single();
            vo.fieldListVo = Db.Queryable<HC_ITEMS, HC_ITEM_EXTEND_REL, HC_EXTEND_FIELD>((o, i, c) => o.ID == i.ITEM_ID && c.ID == i.EXTEND_FIELD_ID)
                .Where(o => o.ID == itemId).OrderBy((o, i, c) => c.SORT, OrderByType.Asc)
                .Select((o, i, c) => new HC_EXTEND_FIELD_VO
                {
                    ExtendFieldID = c.ID,
                    ExtendFieldNAME = c.NAME,
                    ExtendFieldTITLE = c.TITLE,
                    CONTROL_TYPE = c.CONTROL_TYPE,
                    DEFAULT_VALUE = c.DEFAULT_VALUE,
                    IS_REQUIRED = c.IS_REQUIRED
                }
                ).ToList();
            return vo;
        }

        public List<V_HC_ITEM_FIELD_VALUE> Get_FIELD_REFERENCE_VALUE_List(string itemId, int pageIndex, int pageSize, string sortFiled, string orderBy, ref int totalCount)
        {
            return Db.Queryable<V_HC_ITEM_FIELD_VALUE>()
                .OrderBy(sortFiled + " " + orderBy)
                .WhereIF(!string.IsNullOrEmpty(itemId), x => x.ITEM_ID == itemId || SqlFunc.IsNullOrEmpty(x.ITEM_ID))
                .WhereIF(string.IsNullOrEmpty(itemId), x => SqlFunc.IsNullOrEmpty(x.ITEM_ID))
                .ToPageList(pageIndex, pageSize, ref totalCount);
            //var list = Db.Queryable<HC_EXTEND_FIELD, HC_FIELD_REFERENCE_VALUE>((f, v) => f.ID == v.EXTENDFIELDID)
            //    .Where(f => f.ENABLE == 1)
            //    .OrderBy(o => o.ADDTIME)
            //    .Select((f, v) => new FIELD_REFERENCE_VALUE
            //    {
            //        EXTENDFIELDID = f.ID,
            //        TITLE = f.TITLE,
            //        NAME = f.NAME,
            //        VALUEID = v.ID,
            //        BASEVALUE = v.BASEVALUE,
            //        MAXVALUE = v.MAXVALUE,
            //        MINVALUE = v.MINVALUE
            //    }).ToPageList(pageIndex, pageSize, ref totalCount);
            //    //.GetList(pageIndex, pageSize, sortFiled, orderBy, ref totalCount); //Class1是一个新建的类，更多Select用法看下面文档

            ////List<FIELD_REFERENCE_VALUE> list = new List<FIELD_REFERENCE_VALUE>();
            ////List<HC_EXTEND_FIELD> filedList = fieldDal.GetList(pageIndex, pageSize, sortFiled, orderBy, ref totalCount);
            ////FIELD_REFERENCE_VALUE model = new FIELD_REFERENCE_VALUE();
            ////foreach (var item in filedList)
            ////{
            ////    list.Add(new FIELD_REFERENCE_VALUE()
            ////    {
            ////        EXTENDFIELDID = item.ID,
            ////        NAME = item.NAME,
            ////        TITLE = item.TITLE,
            ////        BASEVALUE = 0,
            ////        MINVALUE = 0,
            ////        MAXVALUE = 0
            ////    });
            ////}
            //return list;
        }

        public List<V_HC_ITEM_FIELD_VALUE> Get_FIELD_REFERENCE_VALUE_List(string itemId)
        {
            return Db.Queryable<V_HC_ITEM_FIELD_VALUE>().WhereIF(!string.IsNullOrEmpty(itemId), x => x.ITEM_ID == itemId).OrderBy("SORT").ToList();
        }

        public HC_ITEMS_EXTEND Get_ITEMS_EXTEND(string ItemId)
        {
            var itemModel = Db.Queryable<HC_ITEMS>().Single(it => it.ID == ItemId && it.ENABLE == 1);
            if (itemModel != null)
            {
                HC_ITEMS_EXTEND itemExtendModel = new HC_ITEMS_EXTEND()
                {
                    ID = ItemId,
                    ITEM_NAME = itemModel.ITEM_NAME,
                    ITEM_TITLE = itemModel.ITEM_TITLE,
                    SORT = itemModel.SORT,
                    ENABLE = itemModel.ENABLE,
                };
                var relList = Db.Queryable<HC_ITEM_EXTEND_REL>().Where(it => it.ITEM_ID == ItemId).ToList();
                string relId = string.Empty;
                foreach (var item in relList)
                {
                    relId += item.EXTEND_FIELD_ID + ",";
                }
                itemExtendModel.FIELDS = relId.TrimEnd(',');
                return itemExtendModel;
            }
            else
            { return null; }
        }
    }
}
